/**
 *
 */
package oas.db.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpSession;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import oas.db.LeaveRepository;
import oas.db.WorkcellRepository;
import oas.domain.Leave;
import oas.domain.LeaveType;
import oas.domain.User;
import oas.domain.Workcell;
import oas.web.PaginationSupport;

/**
 * 管理请假DAO
 *
 * @author jtc
 * @version 1.0.0
 */
//注解类作为DAO对象（数据访问对象，Data Access Objects），这些类可以直接对数据库进行操作
//有这些分层操作的话，代码之间就实现了松耦合，代码之间的调用也清晰明朗，便于项目的管理；
@Repository
public class JdbcLeaveRepository implements LeaveRepository {

    private JdbcTemplate jdbc;

    @Autowired
    private WorkcellRepository workcellRepository;

    @Autowired
    public JdbcLeaveRepository(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    /**
     * 取得请假数量
     *
     * @return 请假数量
     */
    @Override
    public long count() {
        // TODO Auto-generated method stub
        return jdbc.queryForLong("select count(id) from `Leave`");
    }

    /**
     * 依据页码和指定页面大小，返回吐槽列表
     * @param pageNo 起始位置
     * @param pageSize 每页数量
     * @return 分页对象
     */
    @Override
    public PaginationSupport<Leave> findPage(int pageNo, int pageSize) {
        int totalCount = (int) count();
        int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
        if (totalCount < 1)
            return new PaginationSupport<Leave>(new ArrayList<Leave>(0), 0);

        List<Leave> items = jdbc.query(SELECT_PAGE_LEAVES, new LeaveRowMapper(), pageSize, startIndex);
        PaginationSupport<Leave> ps = new PaginationSupport<Leave>(items, totalCount, pageSize, startIndex);
        return ps;
    }


    /**
     * 依据id查找请假
     *
     * @param id
     *            请假ID
     * @return 请假信息
     */
    @Override
    public Leave findOne(long id) {
        try {
            System.out.println(SELECT_LEAVE);
            return jdbc.queryForObject(SELECT_LEAVE_UNCHECKED_BY_ID, new LeaveRowMapperUnchecked(), id);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }


    /**
     * 依据id查找已审核请假
     *
     * @param id
     *            请假ID
     * @return 请假信息
     */
    @Override
    public Leave findOneChecked(long id) {
        try {
            System.out.println(SELECT_LEAVE);
            return jdbc.queryForObject(SELECT_LEAVE_BY_ID, new LeaveRowMapper(), id);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }


    /**
     * 新建一个请假
     *
     * @param leave
     *            请假
     * @return 以保存的请假
     */
    @Override
    public Leave save(Leave leave) {
        long id = jdbc.queryForInt("select max(id) from leave") + 1;
        jdbc.update("insert into Spittle (id, applicantId, applyTime, handlerID, "
                        + "handlerTime, status, leaveTypeId) values (?, ?, ?, ?, ?, ?, ?)", id,
                leave.getApplicant().getId(), leave.getApplyDate(), leave.getHandler().getId()
                , leave.getHandleTime(), leave.getStatus(), leave.getType().getId());
        return leave;


    }

    /**
     * 取得指定请假者的历史请假列表
     *
     * @param UserId
     *            请假者ID
     * @return 指定请假者的全部请假
     */
    @Override
    public List<Leave> findByUserId(long UserId) {
        // TODO Auto-generated method stub
        return jdbc.query(SELECT_LEAVES_BY_APPLICANT_ID, new LeaveRowMapper(), UserId);
    }

    /**
     * 管理员取得所有子部门需要审核的请假
     *
     * @param handler
     * @param pageNo
     * @param pageSize
     * @return
     */
    @Override
    public PaginationSupport<Leave> findPageManager(User handler, int pageNo, int pageSize) {
        System.out.println(handler.getWorkcell().getId());
        List<Workcell> workcells = workcellRepository.findWorkcells(handler.getWorkcell());// ;
        workcells.add(handler.getWorkcell());
        int totalCount = 0;
        for (int i = 0; i < workcells.size(); i++) {
            totalCount += (int) countManager(workcells.get(i).getId());
        }

        int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
        if (totalCount < 1)
            return new PaginationSupport<Leave>(new ArrayList<Leave>(0), 0);
        //返回子部门所有的leave，通过handler的workcell查找

        List<Leave> items = new ArrayList<Leave>();

        for (int i = 0; i < workcells.size(); i++) {
            //System.out.println("zheshi"+workcells.get(i).getName());

            items.addAll((List<Leave>) jdbc.query(SELECT_PAGE_LEAVES_MANAGER_UNCHECKED,
                    new LeaveRowMapperUnchecked(), workcells.get(i).getId(), 1000, 0));
        }
        Collections.sort(items, new Comparator<Leave>() {
            @Override
            public int compare(Leave o1, Leave o2) {
                return o1.getApplyDate().after(o2.getApplyDate()) ? -1 : (o1.getApplyDate()
                ).equals(o2.getApplyDate()) ? 1 : 0;
            }
        });
        int flag = startIndex / 10;
        List<Leave> items2 = new ArrayList<Leave>();
        System.out.println("items.size()" + items.size());
        if ((startIndex + 9) / items.size() >= 1) {
            for (int i = startIndex; i < (startIndex + items.size() % 10); i++) {
                items2.add(items.get(i));
            }

        } else {
            for (int i = startIndex; i < (startIndex + 10); i++) {
                items2.add(items.get(i));
            }
        }

        PaginationSupport<Leave> ps = new PaginationSupport<Leave>(items2, totalCount, pageSize, startIndex);
        //System.out.println("zheshi"+items.size());
        //System.out.println(SELECT_PAGE_LEAVES_MANAGER_UNCHECKED);
        return ps;
    }

    /**
     * 管理员取得所有子部门待审核的请假的数目
     * @param handler
     * 			处理请假的人，从session中获取
     * @return
     */
    @Override
    public long countManager(Long Id) {
        // TODO Auto-generated method stub
        //Long Id = handler.getId();
        return jdbc.queryForLong("select count(l.id) from `leave` l ,user a where status = 0 and l.applicantId = a.id and a.workcellId = " + Id);
    }

    /**
     * 管理员取得所有子部门已通过审核的请假
     *
     * @param handler
     * 			处理请假的人从session中获取
     * @param pageNo
     * @param pageSize
     * @return
     */
    @Override
    public PaginationSupport<Leave> findPageCheckedManager(User handler, int pageNo, int pageSize) {

        System.out.println(handler.getWorkcell().getId());
        List<Workcell> workcells = workcellRepository.findWorkcells(handler.getWorkcell());// ;
        workcells.add(handler.getWorkcell());
        int totalCount = 0;
        for (int i = 0; i < workcells.size(); i++) {
            totalCount += (int) countCheckedManager(workcells.get(i).getId());
        }

        int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
        if (totalCount < 1)
            return new PaginationSupport<Leave>(new ArrayList<Leave>(0), 0);
        //返回子部门所有的leave，通过handler的workcell查找

        List<Leave> items = new ArrayList<Leave>();

        for (int i = 0; i < workcells.size(); i++) {
            //System.out.println("zheshi"+workcells.get(i).getName());

            items.addAll((List<Leave>) jdbc.query(
                    SELECT_PAGE_CHECKED_LEAVES_MANAGER,
                    new LeaveRowMapper(), workcells.get(i).getId(), 1000, 0));
        }

        Collections.sort(items, new Comparator<Leave>() {
            @Override
            public int compare(Leave o1, Leave o2) {
                return o1.getApplyDate().after(o2.getApplyDate()) ? -1 : (o1.getApplyDate()
                ).equals(o2.getApplyDate()) ? 1 : 0;
            }
        });
        int flag = startIndex / 10;
        List<Leave> items2 = new ArrayList<Leave>();
        System.out.println("items.size()" + items.size());
        if ((startIndex + 9) / items.size() >= 1) {
            for (int i = startIndex; i < (startIndex + items.size() % 10); i++) {
                items2.add(items.get(i));
            }

        } else {
            for (int i = startIndex; i < (startIndex + 10); i++) {
                items2.add(items.get(i));
            }
        }
        PaginationSupport<Leave> ps = new PaginationSupport<Leave>(items2, totalCount, pageSize, startIndex);
        //System.out.println("zheshi"+items.size());
        //System.out.println(SELECT_PAGE_LEAVES_MANAGER_UNCHECKED);
        return ps;
    }

    /**
     * 管理员取得所有子部门已通过审核的请假的数目
     * @param handler
     * 			处理请假的人，从session中获取
     * @return
     *        已通过请假的人的数量
     */
    @Override
    public long countCheckedManager(Long Id) {
        // TODO Auto-generated method stub
        //Long Id = handler.getId();
        return jdbc.queryForLong("select count(l.id) from `leave` l ,user a where status = 1 and l.applicantId = a.id and a.workcellId = " + Id);
    }


    /**
     * 审核请假条，需要更新审核的人
     *@param id 请假条的Id
     *@param pass 是否批准
     */
    @Override
    public void checkLeave(User handler, Long id, boolean pass) {
        // TODO Auto-generated method stub
        //这里是查询操作
        //置leave的status为审核通过
        SimpleDateFormat timeFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        Date time = new Date();
        String times = timeFormat.format(time);
        if (pass) {
            jdbc.update("update `leave` set status = 1, handleTime ='" + times + "' "
                    + " ,handlerId =" + handler.getId() + " where id= " + id);
        } else {
            jdbc.update("update `leave` set status = 2 ,handleTime ='" + times + "'"
                    + " ,handlerId =" + handler.getId() + " where id= " + id);
        }

        //Leave leave =jdbc.queryForObject(SELECT_LEAVE_BY_ID, new LeaveRowMapper(),id);

    }


    private static final class LeaveRowMapperUnchecked implements RowMapper<Leave> {
        public Leave mapRow(ResultSet rs, int rowNum) throws SQLException {
            Workcell aworkcell = new Workcell(rs.getLong("awid"), rs.getString("awname"),
                    rs.getBoolean("awavailiable"), null);

//			Workcell hworkcell = new Workcell(rs.getLong("hwid"), rs.getString("hwname"),
//					rs.getBoolean("hwavailiable"), null);

            String agender = null;
            if (rs.getBoolean("agender")) {
                agender = "Male";
            } else {
                agender = "Female";
            }
            String arole = null;
            if (rs.getInt("arole") == 1) {
                arole = "Manager";
            } else {
                arole = "User";
            }
//			String hgender = null;
//			if(rs.getBoolean("hgender")) {
//				 hgender = "Male";
//			}else {
//				 hgender = "Female";
//			}
//			String hrole = null;
//			if(rs.getInt("hrole")==1) {
//				hrole = "Manager";
//			}else {
//				hrole = "User";
//			}
            String status = "";
            if (rs.getInt("status") == 0) {
                status = "未审核";
            } else if (rs.getInt("status") == 1) {
                status = "已通过";
            } else if (rs.getInt("status") == 2) {
                status = "未通过";
            }
            User applicant = new User(rs.getLong("applicantId"),
                    rs.getString("ausername"), rs.getString("afullName"),
                    aworkcell, agender, rs.getString("aemail"), rs.getString("aphone"),
                    rs.getInt("aage"), arole, rs.getBoolean("aavailiable"));

//			User handler = new User(rs.getLong("handlerId"),
//		    		rs.getString("husername"), rs.getString("hfullName"),
//		    		hworkcell, hgender, rs.getString("hemail"), rs.getString("hphone"),
//		    		rs.getInt("hage"), hrole, rs.getBoolean("havailiable"));

            LeaveType lt = new LeaveType(rs.getLong("leaveTypeId"), rs.getString("ltdescription")
                    , rs.getBoolean("ltavailiable"));
            return new Leave(rs.getLong("id"), applicant, rs.getTimestamp("applyTime"), new User(),
                    rs.getTimestamp("handleTime"), status, lt);
        }
    }

    private static final class LeaveRowMapper implements RowMapper<Leave> {
        public Leave mapRow(ResultSet rs, int rowNum) throws SQLException {
            Workcell aworkcell = new Workcell(rs.getLong("awid"), rs.getString("awname"),
                    rs.getBoolean("awavailiable"), null);

            Workcell hworkcell = new Workcell(rs.getLong("hwid"), rs.getString("hwname"),
                    rs.getBoolean("hwavailiable"), null);

            String agender = null;
            if (rs.getBoolean("agender")) {
                agender = "Male";
            } else {
                agender = "Female";
            }
            String arole = null;
            if (rs.getInt("arole") == 1) {
                arole = "Manager";
            } else {
                arole = "User";
            }
            String hgender = null;
            if (rs.getBoolean("hgender")) {
                hgender = "Male";
            } else {
                hgender = "Female";
            }
            String hrole = null;
            if (rs.getInt("hrole") == 1) {
                hrole = "Manager";
            } else {
                hrole = "User";
            }
            String status = "";
            if (rs.getInt("status") == 0) {
                status = "未审核";
            } else if (rs.getInt("status") == 1) {
                status = "已通过";
            } else if (rs.getInt("status") == 2) {
                status = "未通过";
            }
            User applicant = new User(rs.getLong("applicantId"),
                    rs.getString("ausername"), rs.getString("afullName"),
                    aworkcell, agender, rs.getString("aemail"), rs.getString("aphone"),
                    rs.getInt("aage"), arole, rs.getBoolean("aavailiable"));

            User handler = new User(rs.getLong("handlerId"),
                    rs.getString("husername"), rs.getString("hfullName"),
                    hworkcell, hgender, rs.getString("hemail"), rs.getString("hphone"),
                    rs.getInt("hage"), hrole, rs.getBoolean("havailiable"));

            LeaveType lt = new LeaveType(rs.getLong("leaveTypeId"), rs.getString("ltdescription")
                    , rs.getBoolean("ltavailiable"));
            return new Leave(rs.getLong("id"), applicant, rs.getTimestamp("applyTime"), handler,
                    rs.getTimestamp("handleTime"), status, lt);
        }
    }

    private static final String SELECT_LEAVE = "select l.* ,"
            + " a.username as ausername,a.workcellid as awid,a.pwd as apwd,a.gender as agender ,"
            + " a.email as aemail, a.phone as aphone ,a.age as aage,a.role as arole ,a.available as "
            + " aavailiable ,a.fullname as afullname,"
            + " h.username as husername,h.workcellid as hwid,h.pwd as hpwd,h.gender as hgender ,"
            + " h.email as hemail, h.phone as hphone ,h.age as hage,h.role as hrole ,h.available as "
            + " havailiable ,h.fullname as hfullname,"
            + " aw.name as awname,aw.available as awavailiable,"
            + " hw.name as hwname,hw.available as hwavailiable ,"
            + "lt.description as ltdescription ,lt.available as ltavailiable "
            + "from `leave` l ,user a,workcell aw,user h,workcell hw,leavetype lt"
            + " where l.applicantId =a.id and a.workcellId ="
            + "aw.id and l.handlerId = h.id and h.workcellid = hw.id and l.leaveTypeId =lt.id";
    private static final String SELECT_LEAVE_UNCHECKED = "select l.*,"
            + " a.username as ausername,a.workcellid as awid,a.pwd as apwd,a.gender as agender "
            + ", a.email as aemail, a.phone as aphone ,a.age as aage,a.role as arole ,a.available as  "
            + "aavailiable ,a.fullname as afullname,"
            + "aw.name as awname,aw.available as awavailiable,"
            + "lt.description as ltdescription ,lt.available as ltavailiable "
            + "from `leave` l ,workcell aw,user a,leavetype lt "
            + "where l.applicantId=a.id  "
            + "and l.leaveTypeId =lt.id and a.workcellId=aw.id ";
    private static final String SELECT_LEAVE_BY_ID = SELECT_LEAVE + " and  l.id=?";
    private static final String SELECT_LEAVE_UNCHECKED_BY_ID = SELECT_LEAVE_UNCHECKED + " and  l.id=?";
    private static final String SELECT_PAGE_LEAVES = SELECT_LEAVE
            + " order by l.applyTime desc limit ? offset  ?";
    private static final String SELECT_LEAVES_BY_APPLICANT_ID = SELECT_LEAVE
            + " and s.id=? order by sp.applyTime desc";

    private static final String SELECT_LEAVE_MANAGER = "select l.* ,"
            + " a.username as ausername,a.workcellid as awid,a.pwd as apwd,a.gender as agender ,"
            + " a.email as aemail, a.phone as aphone ,a.age as aage,a.role as arole ,a.available as "
            + " aavailiable ,a.fullname as afullname,"
            + " h.username as husername,h.workcellid as hwid,h.pwd as hpwd,h.gender as hgender ,"
            + " h.email as hemail, h.phone as hphone ,h.age as hage,h.role as hrole ,h.available as "
            + " havailiable ,h.fullname as hfullname,"
            + " aw.name as awname,aw.available as awavailiable,"
            + " hw.name as hwname,hw.available as hwavailiable ,"
            + "lt.description as ltdescription ,lt.available as ltavailiable "
            + "from `leave` l ,user a,workcell aw,user h,workcell hw,leavetype lt"
            + " where l.applicantId =a.id and a.workcellId ="
            + "aw.id and l.handlerId = h.id and h.workcellid = hw.id and l.leaveTypeId =lt.id "
            + "and a.workcellid=? ";
    //查询待审核请假列表
    private static final String SELECT_LEAVE_MANAGER_UNCHECKED = "select l.*,"
            + " a.username as ausername,a.workcellid as awid,a.pwd as apwd,a.gender as agender "
            + ", a.email as aemail, a.phone as aphone ,a.age as aage,a.role as arole ,a.available as  "
            + "aavailiable ,a.fullname as afullname,"
            + "aw.name as awname,aw.available as awavailiable,"
            + "lt.description as ltdescription ,lt.available as ltavailiable "
            + "from `leave` l ,workcell aw,user a,leavetype lt "
            + "where l.applicantId=a.id and a.workcellid=? "
            + "and l.leaveTypeId =lt.id and a.workcellId=aw.id ";
    private static final String SELECT_PAGE_LEAVES_MANAGER_UNCHECKED = SELECT_LEAVE_MANAGER_UNCHECKED
            + "and l.status =0 order by l.applyTime desc limit ? offset  ?";
    private static final String SELECT_PAGE_LEAVES_MANAGER = SELECT_LEAVE_MANAGER
            + "and l.status =0 order by l.applyTime desc limit ? offset  ?";

    //查询已通过的请假列表
    private static final String SELECT_PAGE_CHECKED_LEAVES_MANAGER = SELECT_LEAVE_MANAGER
            + "and l.status =1 order by l.applyTime desc limit ? offset  ?";
    //private static final String SELECT_LEAVE_BY_ID


}
